package com.xncoding.easypoi.util;
//import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Objects;

public class PoiUtils {

    public static void getCell(MultipartFile file,int shet,int srow ,int scell) throws Exception {
        //创建一个工作铺
//        Workbook workbook = WorkbookFactory.create(file.getBytes());
        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        //获取第一个excel表
        Sheet sheet = workbook.getSheetAt(shet);
        //获取excel的第二行(注意下表从0开始，0代表第一行)
        Row row = sheet.getRow(srow);
        //获取excel的第二列(注意下表从0开始，0代表第一列)
        Cell testCell=row.getCell(scell);
        //第二行第二列的数据
        String test = getXCellVal(testCell);
        System.out.println(test);
    }

    /**
     * 根据单元格的格式 获取单元格的内容
     * @param cell
     * @return
     */
    private static String getXCellVal(Cell cell) {
        DateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        DecimalFormat df = new DecimalFormat("0.0000");
        String val = "";
        switch (cell.getCellType()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    val = fmt.format(cell.getDateCellValue()); //日期型
                } else {
                    val = df.format(cell.getNumericCellValue()); //数字型
                    // 去掉多余的0，如最后一位是.则去掉
                    val = val.replaceAll("0+?$", "").replaceAll("[.]$","");
                }
                break;
            case STRING: //文本类型
                val = cell.getStringCellValue();
                break;
            case BOOLEAN: //布尔型
                val = String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK: //空白
                val = cell.getStringCellValue();
                break;
            case ERROR: //错误
                val = "";
                break;
            case FORMULA: //公式
                try {
                    val = String.valueOf(cell.getStringCellValue());
                } catch (IllegalStateException e) {
                    val = String.valueOf(cell.getNumericCellValue());
                }
                break;
            default:
                val = cell.getRichStringCellValue() == null ? null : cell.getRichStringCellValue().toString();
        }
        return val;
    }

    public static void getrow(MultipartFile file) throws IOException {

        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);

        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        System.out.println("firstRowNum =" + firstRowNum + " \t lastRowNum="+lastRowNum);

        Row row0 = sheet.getRow(0);
        int rowNum = row0.getRowNum();
        System.out.println("rowNum = "+ rowNum);

        short lastCellNum = row0.getLastCellNum();
        short firstCellNum = row0.getFirstCellNum();
        int physicalNumberOfCells = row0.getPhysicalNumberOfCells();
        System.out.println("firstCellNum= "+firstCellNum + "\t lastCellNum = "+ lastCellNum +  " \t physicalNumberOfCells="+physicalNumberOfCells);

    }
    public static void getrow01(MultipartFile file) throws IOException {

        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);

        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        System.out.println("firstRowNum =" + firstRowNum + " \t lastRowNum="+lastRowNum);

        Row row0 = sheet.getRow(0);
        int rowNum = row0.getRowNum();
        System.out.println("rowNum = "+ rowNum);

        short lastCellNum = row0.getLastCellNum();
        short firstCellNum = row0.getFirstCellNum();
        int physicalNumberOfCells = row0.getPhysicalNumberOfCells();
        System.out.println("firstCellNum= "+firstCellNum + "\t lastCellNum = "+ lastCellNum +  " \t physicalNumberOfCells="+physicalNumberOfCells);

        for (int i = 0; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                String s = null ;
                if(Objects.nonNull(cell)){
                    s = cell.toString();
                }
                System.out.print("cell num :"+ j + " cell val :"+ s +" \t");
            }
            System.out.println();
        }

    }

}
